﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Data;
using MySql.Data.MySqlClient;

using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;

namespace DMU
{
    class Service
    {
        public static bool isLogin=false;
        public static string username = "",pass;
        public static string fullname = "";
        
        public static SqlConnection cn;
        public static string myConnect ;
        //Server=112.78.2.141; Database=thpthiep_DMU;Uid=thpthiep_dmucode; Pwd=dmukhai3quy1;
        private SqlCommand cmd;
        
        /// <summary>
        /// Trả về theo tt: 0 server,1 database,2 user,3 pas
        /// </summary>
        public static string[] info;

        help hp = new help();
        public Service()
        {
            if (myConnect == "" || myConnect == null)
            {
                myConnect = getCnString();
               // MessageBox.Show(myConnect);
                info = getInfoConfig();
            }
        }
        

        public bool setCnString(string server,string database,string account,string pas)
        {
            bool trave;
            try
            {
                string tem = string.Format("Server={0}; Database={1};Trusted_Connection=yes;User ID={2};Password={3};", server, database, account, pas);
                
                cn = new SqlConnection(tem);
                cn.Open();
                trave = true;
                cn.Close();
                
                string path = Application.StartupPath+@"\DMUClassLid.dll";
                char c = Convert.ToChar(32);
                string s = server + c + database + c + account + c + pas;
                writeFile(encoding(s), path);
            }
            catch (Exception)
            {
                trave = false;
            }
            return trave;
        }

        /// <summary>
        /// Trả về theo tt: server,database,user,pas
        /// </summary>
        /// <returns></returns>
        public string[] getInfoConfig()
        {
            string path = Application.StartupPath;
            path += @"\DMUClassLid.dll";

            string s = decoding(readFile(path));
            string[] info = s.Split(Convert.ToChar(32));
            return info;
        }

        public string getCnString()
        {
            string st = "";
            try
            {
                string path = Directory.GetCurrentDirectory();
                path += @"\DMUClassLid.dll";
                string s = decoding(readFile(path));

                info = getInfoConfig();
                st = string.Format("Server={0}; Database={1};Trusted_Connection=yes;User ID={2};Password={3};", info[0], info[1], info[2], info[3]);
            }
            catch (Exception)
            {
            }
            return st;
        }
    
        public void openConnect()
        {

            try
            {

                if (cn == null || cn.State == ConnectionState.Closed)
                {
                    cn = new SqlConnection(myConnect);
                    cn.Open();
                }
            }
            catch (Exception et)
            {
                MessageBox.Show("Không thể kết nối tới CSDL !. Vui lòng thiết lập kết nối " + et.Message, "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

        }

        public void closeConnect()
        {
            if (cn.State == ConnectionState.Open)
                cn.Close();
        }

        /// <summary>
        /// Trả về datatable với tên store và các tham số của thủ tục truyền vào 
        /// </summary>
        /// <param name="tenStore"></param>
        /// <param name="pr"></param>
        /// <returns></returns>
        public DataTable getDatatable(string tenStore, params SqlParameter[] pr)
        {
            
            openConnect();
            DataTable dt = new DataTable();
                
            cmd = new SqlCommand(tenStore, cn);
            cmd.CommandType = CommandType.StoredProcedure;
            if (pr != null)
            cmd.Parameters.AddRange(pr);
            IDataReader idr = cmd.ExecuteReader();
            dt.Load(idr);

                cmd.Dispose();
                closeConnect();

            return dt;
        }

        public bool executeNonqueryStore(string tenStore, params SqlParameter[] pr)
        {
            bool rt = false;
            openConnect();

            cmd = new SqlCommand(tenStore,cn);
            cmd.CommandType = CommandType.StoredProcedure;
            if (pr != null)
                cmd.Parameters.AddRange(pr);
            if (cmd.ExecuteNonQuery() >= 1)
                rt = true;

            cmd.Dispose();
            closeConnect();

            return rt;
        }

        //đóng mở kết nối là tùy ở người dùng 

        public bool cusTomExecuteNonqueryStore(string tenStore, params SqlParameter[] pr)
        {
            bool rt = false;

            cmd = new SqlCommand(tenStore, cn);
            cmd.CommandType = CommandType.StoredProcedure;
            if (pr != null)
                cmd.Parameters.AddRange(pr);
            if (cmd.ExecuteNonQuery() >= 1)
                rt = true;

            
            return rt;
        }
       

        /// <summary>
        /// TRả về kết quả của cột đầu tiên trong dòng đầu tiên trong tập kết quả trả về
        /// </summary>
        /// <param name="tenstore"></param>
        /// <param name="pr">vd:select count(at) from a</param>
        /// <returns></returns>
        public string executeScalarStore(string tenstore, params SqlParameter[] pr)
        {
            string s = "";
            openConnect();
            cmd = new SqlCommand(tenstore, cn);
            cmd.CommandType = CommandType.StoredProcedure;

            if (pr != null)
                cmd.Parameters.AddRange(pr);
            
            object j = cmd.ExecuteScalar();
            if (j != null)
                s = j.ToString();

            cmd.Dispose();
            closeConnect();

            return s;
        }

        public string cusTomExecuteScalarStore(string tenstore, params SqlParameter[] pr)
        {
            string s = "";
            cmd = new SqlCommand(tenstore, cn);
            cmd.CommandType = CommandType.StoredProcedure;

            if (pr != null)
                cmd.Parameters.AddRange(pr);

            object j = cmd.ExecuteScalar();
            if (j != null)
                s = j.ToString();

            cmd.Dispose();

            return s;
        }

        public bool executeNonquery(string query)
        {
            bool rt = false;
            openConnect();

            cmd = new SqlCommand(query, cn);
            
            return rt;
        }
        
       public  DataTable excelToDatatable (string ExcelPatch, string query)//(string ExcelPatch,string SheetName )
        {
            string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", ExcelPatch);
            DataTable tb = null;
            try
            {
                //string query = String.Format("select * from [{0}$]", SheetName);
                OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString);
                tb = new DataTable();
                dataAdapter.Fill(tb);
            }
            catch (Exception et)
            {
                hp.showMes("Lỗi khi load dữ liệu từ file excel !. Kiểm tra lại file "+et.Message, 1);
            }
            return tb;

        }

       public string[] getEcelSheetName(string path)
       {
           string[] name=new string[8];
           try
           {
               OleDbConnection kn = new OleDbConnection(string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", path));
               kn.Open();
               DataTable dt = kn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

               name = new string[dt.Rows.Count];
               if (dt != null)
               {
                   int i = 0;
                   foreach (DataRow row in dt.Rows)
                   {
                       name[i] = row["TABLE_NAME"].ToString();
                       i++;
                   }
               }
               kn.Close();
           }
           catch (Exception et)
           {
               hp.showMes("Có lỗi khi load sheetName " + et.Message, 1);
           }

           return name;

       }

       public string encoding(string s)
       {
           string rt = "";
           try
           {
               char[] t = s.ToArray();

               for (int i = 0; i < t.Length; i++)
               {
                   t[i] = Convert.ToChar(Convert.ToInt32(t[i] - 30));
                   rt += t[i].ToString();
               }
           }
           catch (Exception et) { hp.showMes("Lỗi khi mã hóa " + et.Message, 1); }
           return rt;
       }

       public string decoding(string s)
       {
           string rt = "";
           try
           {
               char[] t = s.ToArray();

               for (int i = 0; i < t.Length; i++)
               {
                   t[i] = Convert.ToChar(Convert.ToInt32(t[i] + 30));
                   rt += t[i].ToString();
               }
           }
           catch (Exception et) { hp.showMes("Lỗi khi giải mã " + et.Message, 1); }
           return rt;
       }

       public void writeFile(string text,string path)
       {
           try
           {
               FileStream fs=new FileStream(path,FileMode.Create);
           
               StreamWriter wr = new StreamWriter(fs,Encoding.UTF8);
               wr.WriteLine(text);
               wr.Close();
           }
           catch(Exception et)
           {
               MessageBox.Show("Lỗi ghi file " + et.Message, "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
           }
       }

       public string readFile(string path)
       {
           string s = "";
           try
           {
               if (File.Exists(path))
               {
                   FileStream fs = new FileStream(path, FileMode.Open);
                   StreamReader rd = new StreamReader(fs);
                   s = rd.ReadLine();
                   rd.Close();
               }
               
           }
           catch (Exception et)
           {
               MessageBox.Show("Lỗi đọc file " + et.Message, "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
           }
           return s;
       }

       public void addRow(DataTable dt,string[] data)
       {
           try
           {
               DataRow row = dt.NewRow();
               for (int i = 0; i < data.Length; i++)
               {
                   row[i] = data[i];
               }
               dt.Rows.Add(row);
           }
           catch(Exception et)
           {
               hp.showMes("Lỗi khi refresh dữ liệu ! " + et.Message, 1);
           }

       }

       public void updateRow(DataRow row,string[] data)
       {
           try
           {
               for (int i = 0; i < data.Length; i++)
                   row[i] = data[i];

           }
           catch (Exception et)
           {
               hp.showMes("Lỗi khi refresh dữ liệu ! " + et.Message, 1);
           }
       }

    }
}
